<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzb.erp.api.pc.sys.mapper.CommonMapper">

    <update id="revertData">
        update ${tableName} set deleted = 0 where deleted = 1;
    </update>

    <update id="revertStaff">
        update staff set deleted = 0 and state = 1;
    </update>

    <select id="selectBuilder" resultType="com.hzb.erp.common.pojo.SelectBuilderVO">
        <if test='code == "subject"'>
            SELECT t1.id, t1.name as label FROM subject t1 where t1.deleted = 0 order by t1.id desc
        </if>
        <if test='code == "role"'>
            SELECT t1.id, t1.name as label FROM sys_role t1
        </if>
        <if test='code == "school"'>
            SELECT t1.id, t1.name as label FROM org t1 where `level` = 2 and t1.deleted = 0 order by t1.id desc
        </if>
    </select>

    <select id="loadSelectorLabel" resultType="java.lang.String">
        select
        <choose>
            <when test='code == "staff"'>
                group_concat(t1.name) from staff t1
            </when>
            <when test='code == "teacher"'>
                group_concat(t1.name) from staff t1
            </when>
            <when test='code == "student"'>
                group_concat(t1.name) from student t1
            </when>
            <when test='code == "class"'>
                group_concat(t1.name) from class t1
            </when>
            <when test='code == "classroom"'>
                group_concat(t1.name) from classroom t1
            </when>
            <when test='code == "course"'>
                group_concat(t1.name) from course t1
            </when>
            <when test='code == "subject"'>
                group_concat(t1.name) from `subject` t1
            </when>
            <when test='code == "lesson"'>
                group_concat(t1.title) from `lesson` t1
            </when>
            <when test='code == "org" or code == "school"'>
                group_concat(t1.name) from org t1
            </when>
            <when test='code == "wxaccess"'>
                group_concat(t1.nickname) from wx_access t1
            </when>
            <when test='code == "material"'>
                group_concat(t1.name) from material t1
            </when>
            <otherwise>
            </otherwise>
        </choose>

        <where>
            t1.id in
            <foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

    <select id="checkUnique" resultType="java.lang.Long">
        SELECT id
        FROM ${tableName} where ${field} = #{value} limit 1
    </select>


    <resultMap id="userResultMap" type="hashmap"></resultMap>

    <select id="teacherCenterStatsCount" resultMap="userResultMap">
      select * from (
        select count(0) lesson_count from lesson
        where id in (select DISTINCT lesson_id from lesson_teacher where teacher_id = #{teacherId})
        and DATE_FORMAT( add_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
       ) g1, (

        select count(0) rollcall_count from lesson_student
        where teacher_id = #{teacherId}
        and DATE_FORMAT( sign_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

       ) g2, (

        select count(0) course_count from student_course
        where operator = #{teacherId} and deleted = 0
        and DATE_FORMAT( add_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

       ) g3, (

        select count(0) student_count from student
        where creator = #{teacherId} and deleted = 0
        and DATE_FORMAT( add_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
       ) g4
    </select>
    <select id="dictBuilder" resultType="com.hzb.erp.common.pojo.SelectBuilderVO">
        <choose>
            <when test='code == "position"'>
                SELECT t1.id, t1.name as label FROM `position` t1
            </when>

            <when test='code == "school"'>
                SELECT t1.id, t1.name as label FROM `org` t1 where t1.level = 2
            </when>

            <otherwise>
                SELECT t1.id, t1.name as label FROM dict_item t1
                where t1.dict_id = (select id from dict where code = #{code})
                order by t1.sort_num desc
            </otherwise>
        </choose>
    </select>

    <select id="aotucomplateBuilder" resultType="com.hzb.erp.common.pojo.AutocompleteBuilderVO">

        SELECT t1.id, t1.name as value FROM dict_item t1
        where t1.dict_id = (select id from dict where code = #{code}) and t1.name like concat('%', #{keyword}, '%')
        order by t1.sort_num desc limit 20

    </select>
</mapper>
